package com.cdigtcwww01.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.cdigtcwww01.model.User;
import com.cdigtcwww01.util.DbUtil;

public class UserDao {

    private Connection connection;

    public UserDao() {
        connection = DbUtil.getConnection();
    }

    public void addUser(User user) {
        try {
            PreparedStatement preparedStatement = connection
                    .prepareStatement("insert into int_funcionario (matricula, empresa, cargo, funcao, lotacao, nome, ramal, unidade, prefixo) values (?, ?, ?, ?, ?, ?, ?, ?, ? )");
            // Parameters start with 1
            preparedStatement.setString(1, user.getMatricula());
            preparedStatement.setString(2, user.getEmpresa());
            preparedStatement.setString(3, user.getCargo());
            preparedStatement.setString(4, user.getFuncao());
            preparedStatement.setString(5, user.getLotacao());
            preparedStatement.setString(6, user.getNome());
            preparedStatement.setString(7, user.getRamal());
            preparedStatement.setString(8, user.getUnidade());
            preparedStatement.setString(9, user.getPrefixo());
            preparedStatement.execute();
            preparedStatement.close();
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void deleteUser(String userId) {
        try {
            PreparedStatement preparedStatement = connection
                    .prepareStatement("delete from int_funcionario where matricula=?");
            // Parameters start with 1
            preparedStatement.setString(1, userId);
            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void updateUser(User user) {
        try {
            PreparedStatement preparedStatement = connection
                    .prepareStatement("update int_funcionario set matricula=?, empresa=?, cargo=?, funcao=?, lotacao=?, nome=?, ramal=?, unidade=?, prefixo=?"
            + "where matricula=?");
            // Parameters start with 1 
            preparedStatement.setString(1, user.getMatricula());
            preparedStatement.setString(2, user.getEmpresa());
            preparedStatement.setString(3, user.getCargo());
            preparedStatement.setString(4, user.getFuncao());
            preparedStatement.setString(5, user.getLotacao());
            preparedStatement.setString(6, user.getNome());
            preparedStatement.setString(7, user.getRamal());
            preparedStatement.setString(8, user.getUnidade());
            preparedStatement.setString(9, user.getPrefixo());
            preparedStatement.setString(10, user.getMatricula());
            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public List<User> getAllUsers() {
        List<User> users = new ArrayList<User>();
        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery("select * from int_funcionario");
            while (rs.next()) {
                User user = new User();
                user.setMatricula(rs.getString("matricula"));
                user.setEmpresa(rs.getString("empresa"));
                user.setCargo(rs.getString("cargo"));
                user.setFuncao(rs.getString("funcao"));
                user.setLotacao(rs.getString("lotacao"));
                user.setNome(rs.getString("nome"));
                user.setRamal(rs.getString("ramal"));
                user.setUnidade(rs.getString("unidade"));
                user.setPrefixo(rs.getString("prefixo"));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return users;
    }

    public User getUserById(String userId) {
        User user = new User();
        try {
            PreparedStatement preparedStatement = connection.
                    prepareStatement("select * from int_funcionario where matricula=?");
            preparedStatement.setString(1, userId);
            ResultSet rs = preparedStatement.executeQuery();

            if (rs.next()) {
            	user.setMatricula(rs.getString("matricula"));
                user.setEmpresa(rs.getString("empresa"));
                user.setCargo(rs.getString("cargo"));
                user.setFuncao(rs.getString("funcao"));
                user.setLotacao(rs.getString("lotacao"));
                user.setNome(rs.getString("nome"));
                user.setRamal(rs.getString("ramal"));
                user.setUnidade(rs.getString("unidade"));
                user.setPrefixo(rs.getString("prefixo"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return user;
    }
}